import xlrd

data = xlrd.open_workbook("D:\\HR\\5.baidu-员工的人员信息.xls")
# 查看工作表
sheets = data.sheet_names()
# data.sheet_by_index(0)获取选项卡的名称
sheet = data.sheet_by_name("人员管理")
data = sheet.row_values(0) # 获取第一行数据
print(data)
# print(sheets)  sheet.nrows
count = 0
yidong = 0
liantong = 0
dianxin = 0
man = 0
wuman = 0
age = 0
highsalary = 0
shortsalary = 0
video = 0
danger = 0
for i in range(1,sheet.nrows):
    # 输出每一行数据
    # print(sheet.row_values(i))
    # 取出电话号码并取出前四位
    phon = sheet.row_values(i)[5]
    typ = int(phon[0:4])

    # 判断属于那家
    if 1340<=typ<1400 or 1470<=typ<1480 or 1500<=typ<1530 or 1570<=typ<1600 or 1780<=typ<1790 or 1820<=typ<1850 or 1880<=typ<1890 or typ == 1703 or typ == 1705 or typ == 1706:
        yidong += 1
    elif 1300<=typ<1330 or 1450<=typ<1460 or 1550<=typ<1570 or 1750<=typ<1770 or 1850<=typ<1870 or 1710<=typ<1720 or typ == 1704 or typ == 1707 or typ == 1708 or typ == 1709:
        liantong += 1
    else:
        dianxin += 1

    # 公司男女人数
    sex = sheet.row_values(i)[8]
    if sex == "男":
        man += 1
    else: wuman += 1

    # 年龄超过45
    a = sheet.row_values(i)[7]
    if int(a)>45:
        age += 1

    # 薪资过8k和低于3k
    s = sheet.row_values(i)[11]
    if int(s)>8000:
        highsalary += 1
    elif int(s)<3000:
        shortsalary += 1

    # 判断高危地区
    are = sheet.row_values(i)[9]
    d = are[0:3]
    if d == "北京市" or d == "黑龙江"or d == "四川省"or d == "福建省":
        danger += 1

    # 传媒公司多少人
    s = sheet.row_values(i)[13]
    if "传媒" in s:
        video += 1
    # 计算总人数
    count +=1



print(f"表格内共有{count}人")
print(f"移动用户数量{yidong}、"f"所占百分比为{(yidong/count*100):2f}%")
print(f"联通用户数量{liantong}、"f"所占百分比为{(liantong/count*100):2f}%")
print(f"电信用户数量{dianxin}、"f"所占百分比为{(dianxin/count*100):2f}%")
print(f"总公司男共有{man}人")
print(f"总公司女共有{wuman}人")
print(f"总公司共有{age}人超过45岁")
print(f"总公司共有{highsalary}人月薪资超过8000")
print(f"总公司共有{shortsalary}人月薪资低于3000")
print(f"总公司共有{danger}人在危险地区")
print(f"总公司共有{video}人在传媒公司")
